package dao;


import model.ExcellentStudent;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ExcellentStudentCanvasDao {

    private ExcellentStudentCanvasDao() {

    }

    public static List<ExcellentStudent> getExcellentStudents() throws SQLException {
        List<ExcellentStudent> list = new ArrayList<>();

        //优秀学生，所选各科final_score均大于60分
        String sql = "select score.s_no,student.s_name " +
                "from score,student " +
                "where score.s_no=student.s_no " +
                "group by score.s_no " +
                "having min(final_score)>=60";

        PreparedStatement stmt = Dao.getConnection().prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            String sno = rs.getString(rs.findColumn("s_no"));
            String sname = rs.getString(rs.findColumn("s_name"));

            ExcellentStudent excellentStudent = new ExcellentStudent();
            excellentStudent.setSno(sno);
            excellentStudent.setSname(sname);
            list.add(excellentStudent);
        }

        return list;
    }

}
